#include "widget.h"
#include "ui_widget.h"

#include <QSqlDatabase>
#include <QDebug>
#include <QSqlError>
#include <QSqlQuery>

Widget::Widget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Widget)
{
    ui->setupUi(this);

    // 查看默认支持的驱动
//    qDebug() << QSqlDatabase::drivers();

    // 连接MySQL服务器
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("root");
    db.setDatabaseName("abc");
    if(db.open())
    {
        qDebug() << "连接成功";
    }
    else
    {
        qDebug() << "连接失败: " << db.lastError().text();
    }

    // 执行SQL语句
    QSqlQuery query;
//    query.exec("create database abc;");
//    query.exec("use abc;");

//    // id(主键)  account passwd gender age
//    QString sql = "create table member("
//                  " id int unsigned not null primary key auto_increment,"
//                  " account varchar(30) unique not null,"
//                  " passwd varchar(10) not null,"
//                  " gender enum('男', '女'),"
//                  " age tinyint unsigned not null"
//                  ")engine=MyISAM default charset=utf8";

//    query.exec(sql);

//    query.exec("drop table member");
//    query.exec("drop database abc");

    // 插入数据
//    QString sql = "insert into member(id, account, passwd, gender, age) values(1, 'zss', '123123', '男', 8);";
//    QString sql = "insert into member values(null, 'lss', '123456', '女', 10)";
//    query.exec(sql);


    // 批量插入 - 1
//    // 1) 执行预处理
//    QString sql = "insert into member values(null, ?, ?, ?, ?)";
//    query.prepare(sql);

//    // 2) 准备数据
//    QStringList accList = {"小明", "小红", "花花"};
//    QStringList pwdList = {"111111", "222222", "333333"};
//    QStringList genderList = {"男", "女", "女"};
//    QVariantList ageList = {10, 15, 12};

//    // 3) 绑定数据
//    query.addBindValue(accList);
//    query.addBindValue(pwdList);
//    query.addBindValue(genderList);
//    query.addBindValue(ageList);

//    // 4) 执行插入
//    query.execBatch();


    // 批量插入数据 - 2
//    // 1) 执行预处理
//    QString sql = "insert into member values(null, :acc, :pwd, :gen, :age)";
//    query.prepare(sql);

//    // 2) 准备数据
//    QStringList accList = {"韩梅梅", "李磊"};
//    QStringList pwdList = {"123456", "654321"};
//    QStringList genderList = {"女", "男"};
//    QVariantList ageList = {12, 13};

//    // 3) 绑定数据
//    query.bindValue(":acc",  accList);
//    query.bindValue(":pwd", pwdList);
//    query.bindValue(":gen", genderList);
//    query.bindValue(":age", ageList);

//    // 4) 执行插入
//    query.execBatch();


    // 修改
//    QString sql = "update member set account='李思思', passwd='111222' where id=2";
//    query.exec(sql);

    // 查询
//    QString sql = "select * from member";
//    query.exec(sql);
    // 循环从查询的结果集中得到每一行的数据
//    while (query.next())
//    {
//        qDebug() << query.value(0).toInt() << query.value("id").toInt() <<
//                    query.value(1).toString() << query.value("account").toString() <<
//                    query.value(2).toString() << query.value("passwd").toString() <<
//                    query.value(3).toString() << query.value("gender").toString() <<
//                    query.value(4).toInt() << query.value("age").toInt();
//    }

//    ui->tableWidget->setRowCount(query.numRowsAffected());
//    ui->tableWidget->setColumnCount(5);
//    ui->tableWidget->setHorizontalHeaderLabels({"id", "account", "passwd", "gender", "age"});
//    int row = 0;
//    while (query.next())
//    {
//        ui->tableWidget->setItem(row, 0, new QTableWidgetItem(QString::number(query.value("id").toInt())));
//        ui->tableWidget->setItem(row, 1, new QTableWidgetItem(query.value("account").toString()));
//        ui->tableWidget->setItem(row, 2, new QTableWidgetItem(query.value("passwd").toString()));
//        ui->tableWidget->setItem(row, 3, new QTableWidgetItem(query.value("gender").toString()));
//        ui->tableWidget->setItem(row++, 4, new QTableWidgetItem(QString::number(query.value("age").toInt())));
//    }

    // 删除
//    QString sql = "delete from member where age > 12";
//    query.exec(sql);
//    qDebug() << query.numRowsAffected();

    // 查看最后一次SQL操作的错误
    qDebug() << query.lastError().text();
}

Widget::~Widget()
{
    delete ui;
}
